Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / db3tips1.arc / DB3NOTES.TXT < prev next >

Wrap

Text File | 1985-11-30 | 36.8 KB | 876 lines

dBASE III Printer Check (PC Magazine Vol 4 No 7 April 2, 1985 PC Tutor) To determine, from within dBASE III, whether the printer is online before the SET PRINT ON command is issued, you can create a small PRNCHECK.COM program that will check the printer status and return an error level of 1 if an error occurs, 0 if not. Use DEBUG to create PRNCHECK.COM. The program calls the printer interrupt, thus requesting printer status. It then checks for an error signal, which occurs either if bits 5 or 3 are high (1) or if bit 4 is low (0). The program sets the ERRORLEVEL 1 flag if the printer is off-line or not connected. To implement PRNCHECK.COM, put it in a batch program: A>COPY CON:PRNTEST.BAT PRNCHECK IF ERRORLEVEL 1 GOTO SETERR ECHO STORE 'N' TO PRNANS >PRNSET.PRG GOTO END :SETERR ECHO STORE 'Y' TO PRNANS >PRNSET.PRG :END The batch program calls PRNCHECK. If no printer error was generated, it makes a file named PRNSET.PRG, containing the line STORE 'N' TO PRNANS. If there was a printer error, the file contains the line STORE 'Y' TO PRNANS instead. Finally, your sequence of dBASE III commands is: RUN PRNTEST DO PRNSET This will run PRNTEST (setting up the file PRNSET.PRG to look like a dBASE command), then DO the command, so setting up a variable named PRNANS with value 'N' if there was no printer error, or 'Y' is a printer error occurred. Your dBASE program can then check the value of PRNANS and do whatever you like. Create PRNCHECK.COM with DEBUG as follows: A>DEBUG PRNCHECK.COM file not found -A100 xxxx:0100 MOV DX,0000 xxxx:0103 MOV AH,02 xxxx:0105 INT 17 xxxx:0107 TEST AH,28 xxxx:010A JNZ 0118 xxxx:010C MOV AL,10 xxxx:010E AND AH,AL xxxx:0110 CMP AH,AL xxxx:0112 JNE 0118 xxxx:0114 MOV AL,00 xxxx:0116 JMP 011A xxxx:0118 MOV AL,01 xxxx:011A MOV AH,4C xxxx:011C INT 21 xxxx:011E <enter> - -RCX CX 0000 :20 -W Writing 20 bytes -Q ----------------------------------------------------------------- dBASE III to Symphony (PC World Star-Dot-Star July 1984) Many people making the transition from dBASE II and 1-2-3 to dBASE III and Symphony have probably run into the same problem I did: Converting data from dBASE III to Symphony is not nearly as easy as converting from dBASE II to 1-2-3. My solution is as follows. First, run dBASE III and USE or SELECT a data file, then use the command COPY FILE filename.DBF TO filename.EXE DELIMITED to create a fily Symphony can read. Quit dBASE III and start Symphony. From the Services menu (<F9>) select File Import Structured, and then enter the name of the file created in prior step. All fields in the dBASE III file will be imported. I save time by using dBASE III to eliminate the fields I will not need before importing the file with Symphony. To do this, either modify the structure of the data base file before copying it or copy only the selected fields. ----------------------------------------------------------------- dBASE III Lines Up (PC World Star-Dot-Star July 1985) The dBASE III routines CENTER.PRG and RIGHT.PRG will center or right-justify a line of text sent to the printer or the screen. The first line of each routine establishes the parameters to be passed when the program is called. The next line sets the width of the output device, and the two lines after that preserve the cursor position when the routine is called. The last line restores the cursor to its previous location. To use these routines type DO CENTER WITH row,string or DO RIGHT WITH row,string, where row is set to the desired line and string is the text to center or right-justify. * CENTER.PRG PARAMETERS ROW,STRING WIDTH=80 R=ROW() C=COL() @ ROW,0 + (WIDTH - (LEN(STRING)))/2 SAY STRING @ R,C, SAY '' * RIGHT.PRG PARAMETERS ROW,STRING WIDTH=80 R=ROW() C=COL() @ ROW,0 + (WIDTH - (LEN(STRING))) SAY STRING @ R,C SAY '' ----------------------------------------------------------------- dBASE III Timer (PC World Star-Dot-Star July 1985) dBASE III has several functions and field types not available in dBASE II. The TIME() function can be used to set up a timer, as shown in TIMER.PRG. This small program can be run from within dBASE III. Type START = TIME() <Enter> to begin timing; type END = TIME() <Enter> to indicate the end of the interval to be measured. The command DO TIMER will execute the program, which will clear the screen and display the date and the elapsed time in "Saturday: March 16, 1985" format. By issuing the DO TIMER command at various points, START can be stated once and END stated several times to time several different aspects of a program. * TIMER.PRG. Assumes calling program executes START = TIME() * and END = TIME() prior to DO TIMER. SET TALK OFF STORE .T. TO GONOGO DO WHILE GONOGO HOUR1 = VAL(START) HOUR2 = VAL(END) MIN1 = VAL(SUBSTR(START,4,2)) MIN2 = VAL(SUBSTR(END,4,2)) SEC1 = VAL(SUBSTR(START,7,2)) SEC2 = VAL(SUBSTR(END,2,2)) ELAPHR = HOUR2 - HOUR1 IF MIN2 > MIN1 ELAPMIN = MIN2 - MIN1 ELSE ELAPMIN = 60 - MIN1 + MIN2 ELAPHR = ELAPHR - 1 ENDIF IF SEC2 > SEC1 ELAPSEC = SEC2 - SEC1 ELSE ELAPSEC = 60 - SEC1 + SEC2 ELAPMIN = ELAPMIN -1 ENDIF ELAPSE=STR(ELAPHR,2)+":"+STR(ELA;MIN,2)+":"+STR(ELAPSEC,2) DDAY = CDOW(DATE()) + ": " DDATE=CMONTH(DATE())+STR(DAY(DATE()),3)+","+STR(YEAR(DATE()),5) HDR = DDAY + DDATE CLEAR @ 3,10 SAY HDR @ 5,10 SAY "Elapsed time was " @ 5,28 SAY ELAPSE STORE .F. TO GONOGO ENDDO ----------------------------------------------------------------- dBASE III Function Keys (PC World September 1985 Star-Dot-Star) dBASE III has a SET FUNCTION command for defining the characters sent by functions keys F2 through F10. (Changing the value of F1 is not allowed.) You can use this facility to install menu selections via functions keys as demonstrated by the program DKEYS. The first section of the program sets the values of function keys F2 through F7 to letters A through F, representing the six functions represented by the menu. The value of F8 is set to Z so that the program can recognize the F8 key and indicate an error if it is pressed. F9 and F10 are set to QUIT and EXIT, respectively. After setting the function key values, the program displays the menu and waits for a keystroke. A CASE statement executes the selected function, then returns control to the menu. DKEYS: SET FUNCTION 2 TO 'A' SET FUNCTION 3 TO 'B' SET FUNCTION 4 TO 'C' SET FUNCTION 5 TO 'D' SET FUNCTION 6 TO 'E' SET FUNCTION 7 TO 'F' SET FUNCTION 8 TO 'Z' SET FUNCTION 9 TO 'Q' SET FUNCTION 10 TO 'X' SET TALK OFF SET BELL OFF STORE .t. TO cust_menu CLEAR DO WHILE cust_menu STORE ' ' TO func_sel @ 1,34 SAY "Customer Menu" @ 2,37 SAY DATE() @ 5,24 SAY 'F2 Open a New Customer Account' @ 7,24 SAY 'F3 Modify a Customer Account' @ 9,24 SAY 'F4 Display Customer Accounts' @ 11,24 SAY 'F5 Delete a Customer Account' @ 13,24 SAY 'F6 Print an Account Summary Report' @ 15,24 SAY 'F7 Print a Detailed Account Report' @ 17,24 SAY 'F9 Display Prior Menu' @ 19,24 SAY 'F10 Exit to DOS' @ 23,24 SAY 'Select Desired Option:' @ 23,48 SAY func_set @ 23,48 GET func_set READ DO CASE CASE func_sel = 'A' DO newacc CASE func_sel = 'B' DO modacc CASE func_sel = 'C' DO dspacc CASE func_sel = 'D' DO delacc CASE func_sel = 'E' DO accsum CASE func_sel = 'F' DO accdet CASE func_sel = 'X' STORE .f. to cust_menu LOOP CASE func_sel = 'Q' QUIT ELSE @ 0,0 SAY CHR$(7) ENDCASE CLEAR ENDDO cust_menu ----------------------------------------------------------------- Calculating Square Roots (PC Magazine Vol 4 No 23 Nov 12, 1985 Power User) The following is an old Pascal routine for calculating square roots converted into dBASE. Simply STORE the number you would like to a memory variable "number" and execute the procedure with DO SQRT. The result will be returned in the variable "sqrt." This result will be accurate to at least eight decimal places, perhaps more, depending on the precision of the initial number. Editor's Note: Converting Pascal routines into dBASE sounds like an avenue with all sorts of possible applications. There are thousands of Pascal routines to solve many of the problems dBASE programmers face, making this one to avoid rediscovering the wheel. The syntax of each of the two languages is similar enough to make Pascal an attractive source of ideas for .PRG files. Expect a lot of debugging though. The program below is modified slightly to add both an INPUT line at the top and a "?" line at the end. These lines make the program usable as a standalone method for obtaining square roots. To use the routine within a more complex application, you can drop these lines if the preceding code creates a memory variable called "number". * SQRT.PRG -- finds square roots [dBASE III] * ENTER WITH number = NUMBER * RETURNS WITH sqr = SQUARE ROOT SET HEADING OFF SET SAFETY OFF SET TALK OFF INPUT "NUMBER? " TO NUMBER STORE NUMBER * 1.00000000 TO LQnum STORE (1 + LQnum) / 2 TO LQs STORE LQnum * .00000001 TO LQtest STORE .N. TO LQdone DO WHILE .NOT. LQdone STORE LQs * LQs - LQnum TO lqHOLD IF LQhold < 0 STORE 0 - LQhold TO LQhold ENDIF IF LQhold < LQtest STORE .Y. TO LQdone LOOP ENDIF STORE (LQs + (LQnum / LQs)) / 2 TO LQs ENDDO STORE LQs TO SQR ? "ANSWER: " ?? SQR RELEASE ALL LIKE LQ* SET TALK ON RETURN ----------------------------------------------------------------- Stripping WordStar's High Bits (PC Magazine Vol 4 No 26 Dec 24, 1985 Power User) WordStar users with dBASE II have an easy way to strip the high bits the word processor tacks on to the end of each word in a document. Simply boot up dBASE II and load the WordStar file into dBASE's own editor by typing MODIFY COMMAND [filename.ext]. Since dBASE's editor assumes a .PRG extension, you must be sure to add your document's extension or a period after the filename if there is no extension. Then simply resave the file by hitting Ctrl-W. The resulting new file will be pure ASCII. This has workd with documents up to 30K bytes in size. dBASE will not strip out printer formatting commands, which you must take out separately. With dBASE III the process is a bit more complicated. First of all, dBASE III will truncate documents larger than about 4K bytes. Longer documents must be broken into 4K-byte segments, converted into ASCII, then reassembled using WordStar's nondocument mode. Also, unlike dBASE II, dBASE III won't strip out the high bits unless you actually change the file in some way while in MODIFY COMMAND. One way to get around this is to insert a blank line at the top of the file using Ctrl-N, delete it again with Ctrl-Y, then save the file with Ctrl-W. This converts the actual text into ASCII. However, there's still one more step left. dBASE II's MODIFY COMMAND automatically converts WordStar's "soft" carriage returns into regular (hard) ones, but dBASE III will not. To convert the carriage returns, open the document in WordStar's nondocument mode. At the top of the file, type Ctrl-QA (search and replace). At the "FIND?" prompt, hold down the Alt key and type 141 on the numeric keypad, then release the Alt key and hit Enter. At the "REPLACE WITH?" prompt, type Ctrl-P and press the Enter key twice. At the "OPTIONS?" prompt, type NG and hit Enter. This will put a hard carriage return at the end of each line. Editor's Note: This tip is handy for converting WordStar files into something legible under DOS's TYPE command and can save you the cost of a conversion utility if you already own and use both programs. Some dBASE uers may be surprised to find that the program's editor can be used with files other than the default .PRG ones. This permits you to modify .FMT, .FRM and .MEM files, as well as any other ASCII files you wish from within dBASE. ----------------------------------------------------------------- Concurrent Data Base Management (IBM PC Update Dec 1984 by J. D. Carrabis) Ashton-Tate makes much ado about dBASE III's knack for simultaneous data bases...up to 10 active data bases. But you will usually get an error message (TOO MANY FILES OPEN) on about the third data base reference when using a computer with less that 512k of memory. There is no problem running dBASE III on an XT. You copy all the files on the disks in the dBASE package to the hard disk, then place the dBASE factory SYSTEM disk in the floppy drive when you want to use dBASE. If, however, you are using a floppy drive system, you may hit some snags. You need two disks to boot the system. One disk should have a copy of DOS, the DBASE.EXE file, and the two configuration files (CONFIG.DB and CONFIG.SYS). More importantly, the disk that has these three files must be the SYSTEM disk that came with your dBASE package. This is easily done. You first copy all the other files (ASSIST.HLP, DBASE.OVL, HELP.DBS) from your SYSTEM disk to a blank formatted disk. Erase those same files from the dBASE SYSTEM disk. Use the DOS SYS command to place the DOS system on the dBASE SYSTEM disk. Now you have a bootable copy of dBASE III. Of course, when you boot up dBASE III, it asks you for the disk containing the overlay files -- that is the disk with the remaining dBASE files on it (the disk with ASSIST.HLP, DBASE.OVL, HELP.DBS). Take out the SYSTEM disk and place the new disk in. Now you can use dBASE III. But if you want to use the dBASE III RUN command, you have to place another file on your disk, the DOS COMMAND.COM file. You must copy this file to the new disk as well. In case you haven't used it yet, RUN is the dBASE III command that allows you to call programs external to dBASE. An example would be running WordStar from inside dBASE. What does this have to do with not being able to access more than two data bases simultaneously? You have to edit the CONFIG. SYS file so that it reads: FILES=20 BUFFERS=24 But even this doesn't usually work. The solution? Get more memory. What happens then? Every time a file is opened, dBASE III loses some room. You may notice the line FILES=20 in the CONFIG.SYS file. This line tells dBASE that is has a maximum of 20 files it can work with at a given time. Those 20 files get eaten quickly. You start with DOS -- one file. Next is DBASE.EXE -- two. You also need DBASE.OVL -- three files. Adding HELP.DBS makes four. Add a few data bases and their index files; a dBASE program file or two, each with a subroutine; a format file; a new data base created from the old ones; etc. and 20 files are gone. I did have fun with the CONFIG.DB file. This file allows you to customize the features of dBASE III. I also use a special AUTOEXEC.BAT file to facilitate the use of my computer, and a special CONFIG.DB file to facilitate the use of dBASE. Figures 1 and 2 show two separate files to boot up automatically with dBASE. The first is AUTOEXEC.BAT, and the second is CONFIG.DB. Figure 3 is an enhanced CONFIG.DB file that I use. The first line of the AUTOEXEC.BAT tells DOS to change from the starting directory to the dBASE directory. By first creating a dBASE directory, then accessing it, the drives are clean for possible future uses. Next I tell the computer to use a greater than symbol (>) when it gives me a system prompt. This is done with $G. I also have it tell me what directory (or path) I'm in with $P. Then I tell the computer immediately to stop whatever it's doing when I press Ctrl- Break. This is a nice feature and one that is toggled off in most cases. I like to bring things to a crashing halt on occasion. The next 7 lines are simply REMarks that I want printed on the screen. Remember that dBASE III wants to see its own SYSTEM disk when it begins working. These 7 REMarks increase the probability that it will get its SYSTEM disk. The PAUSE command is just that. It stops execution until a key is pressed. The last line in the AUTOEXEC.BAT file is the command to start using dBASE itself. When the time comes that you want to use something other than dBASE, you can make required changes to the AUTOEXEC.BAT file. As an example, let's say you set up your disk with directories for WordStar, Lotus, dBASE, BASIC and BASICA, DOS, LISP, FORTH, R:Base, Macros and Assemblers, P FORTRAN, and COBOL, as I have. I normally don't want to go between these areas. The work I do in one area is meant to stay in that area so the CHDIR\ command is adequate to get me where I want to be. There are times, however, that I don't want to leave my DOS directory but do want to get files from other directories. My own AUTOEXEC.BAT file has: PATH\DOS;\WS;\123;\DB;\BASICS;\L;\F1;\RB;\MACASS;\P;\F2;\C1 in place of the CHDIR/DBASE command. The above PATH command tells DOS to look through all my directories to find what I'm looking for. Needless to say, I also don't have the REMark or PAUSE commands. I have something else in their place. Now for the CONFIG.DB file. Normally, I work with an XT, and most of my programs and data bases are located on it, so I set the default drive to C. I find the BELL annoying in the extreme, so I shut it off. Nor do I like to know everything dBASE does as it does it. I shut that off with TALK. I turn off the HEADINGS and SAFETY because I design my programs with their own headings and backup procedures. I find the DELIMITER distracting so that becomes historical. I do want to have the last chance at changing things, so I want to CONFIRM my answers. The ALTERNATE = C:DEBUG creates a separate dBASE DEBUG file that you can review to find out how far you got and what happened before you bombed out of a program. My own CONFIG.DB file is in Figure 3. Note that I've included a PATH for dBASE to search if I forget to tell it where something is. I also want to be able to escape from what I'm doing when I panic. I like things personal, hence the PROMPT. PW.COM is PERFECT WRITER, a word processing program that I like enough to use for editing my dBASE program files and memo fields. There are quite a few other modifications or adjustments you can make with the AUTOEXEC.BAT, CONFIG.SYS and CONFIG.DB files. The best way to get some use out of these files is to experiment. Figure 1: AUTOEXEC.BAT CHDIR\DBASE PROMPT $P$G BREAK ON REM REM ************************************************************ REM * * REM * PLACE THE DBASE SYSTEM DISK IN DRIVE A * REM * * REM ************************************************************ REM PAUSE DBASE - - - - - - - - - - Figure 2: CONFIG.DB DEFAULT = C: BELL = OFF TALK = OFF HEADINGS = OFF SAFETY = OFF DELIMITER = OFF CONFIRM = ON ALTERNATE = C:DEBUG EXACT = ON - - - - - - - - - - Figure 3: A personalized CONFIG.DB file DEFAULT = C: PATH = C:\DB;\WS;\RB;\DOS BELL = OFF TALK = OFF HEADINGS = OFF SAFETY = OFF DELIMITER = OFF CONFIRM = ON ESCAPE = ON PROMPT = What now, chum? TEDIT = PW.COM WP = PW.COM ----------------------------------------------------------------- dBASE III on the RUN (PC World October 1985 Star-Dot-Star) The dBASE III RUN command can be used to execute programs and DOS commands. For example, the command RUN CD\ will make the root directory the current directory. The command RUN DIR will display the names of the files in the current directory. Even more powerful is the RUN command's ability to execute other files, including batch files. Thus, the command RUN ABC will execute the batch file ABC.BAT, which can contain a list of commands to be executed in sequence. ----------------------------------------------------------------- Converting Symphony Files (PC Magazine Vol 4 No 23 Nov 12, 1985 Power User) Converting a Symphony file for use with dBASE III entails more than either Lotus's or Ashton-Tate's documentation would lead you to believe. The problems stem from the differences between dBASE II and dBASE III. Lotus provides a function, Translate, to go from either Symphony or 123 to dBASE II. Presumably, one should be able to use the dCONVERT utility to convert the dBASE II file upward to dBASE III. Not so. Therefore, after converting a Lotus file via the Translate facility, enter dBASE II (assuming you have dBASE II), USE the file, then QUIT. This resaves the file, adding whatever was missing to make it a proper .DBF file. dCONVERT will now be able to successfully convert the file upward to dBASE III. The Lotus Translate facility is supposed to allow you to convert either a complete worksheet or a subset range within the worksheet to a .DBF file. Unfortunately, in the original release version of the Translate facility, the range conversion option contains some bugs that prevent it from working properly. You must first do a File Extract of your spreadsheet for the desired range, then use Translate's worksheet option to convert the Extract. Prior to converting a Lotus database, all numeric record fields must be individually formatted, via the Cell Format option, to the number of decimal places required. The Global Format option within Symphony is not sufficient for decimal place designations. If you've used the Global Format option to set decimal length, Translate will truncate all decimals into a whole number. The solution is to format all database cells to their required decimal length prior to attempting any conversion into a .DBF file. Numeric Lotus files that contain a label prefix will not convert properly into .DBF files. In order to check each database cell within a spreadsheet for an unwanted label prefix, the following Symphony macro comes in handy: {down}{recalc C8} {if +C8 = "L"}~{beep}{quit} {branch \X} The macro is named \X and the cell C8 contains the formula: @CELLPOINTER("TYPE"). ----------------------------------------------------------------- PC Magazine Index on Disk (PC Magazine Vol 4 No 23 Nov 12, 1985 Power User) The indexes to PC Magazine, available from the Interactive Reader Service (IRS), contain a wealth of data. The value of this data can be enhanced with the command PCMAGNDX.PRG, which loads the index files into a dBASE data file. Once in dBASE's .DBF format, the indexes become powerful tools for tapping the treasures of PC Magazine. Any article can be located in seconds by author, subject or title, and groups of articles by the same author or covering the same topic can be located quickly. For readers who are not familiar with the index text files, they are set up as paired A and B files for each section of an index. The A file contains an article's title, description, volume, issue number and page number. The corresponding B file contains the author's name and the category of the article, such as graphics, networks, etc. On the IRS, the files are identified by the format VOL#A?.TXT and VOL#B?.TXT where "#" indicates the volume of the index (Volume 3 or 4), and "?" is a code indicating the range of issues included within the file (1 for issues 1-5, 2 for 6-12, etc.). Thus, the file pair VOL4A1.TXT and VOL4B1.TXT form a complete nidex for PC Magazine's Volume 4 Numbers 1 through 5. Before using PCMAGNDX.PRG, you must download paired copies of the index files from the IRS. Then, using any ASCII text editor, delete from both files the first five lines, which contain page and column headings. Otherwise, these headings will end up as records in the resulting dBASE file. Next, use dBASE to CREATE the three .DBF files TEMPA, TEMPB and PCMAGNDX. Use the exact field lengths as show, as they are critical. Finally, run PCMAGNDX.PRG by typing DO PCMAGNDX. The result will be the file PCMAGNDX.DBF containing the combined information from the original A and B text files. The program will automatically append new index file pairs to the database you've already created as new indexes become available on the IRS. The files TEMPA.DBF and TEMPB.DBF will be emptied of records but will remain on the disk so they need not be recreated each time PCMAGNDX.PRG is run. * PCMAGNDX.PRG - converts PC Magazine's Index Files to dBASE files SET HEADING OFF SET SAFETY OFF CLEAR SET TALK OFF ACCEPT "Enter Volume Number (the First # in VOL#A#.TXT) " TO V ACCEPT "Enter Issue Code (the Second # in VOL#A#.TXT) " TO N CLEAR @ 12,25 SAY "Processing - Please wait" SELECT A USE pcmagndx SELECT B USE tempA APPEND FROM VOL&V.A&N..TXT SDF GO TOP @ 13,25 SAY "CREATING RECORD: " DO WHILE .NOT. EOF() STORE SUBSTR(title,2,32) TO Mtitle STORE SUBSTR(subject,2,35) TO Msubject STORE vol TO Mvol STORE no TO Mno STORE page TO Mpage SKIP DO WHILE.NOT.EOF().AND.vol=" " STORE TRIM(Mtitle)+title TO Mtitle STORE TRIM(Msubject)+subject TO Msubject SKIP ENDDO SELECT A @ 13,52 SAY STR(RECNO(),4,0) APPEND BLANK REPLACE title WITH TRIM(Mtitle),subject WITH TRIM(Msubject),; vol WITH Mvol,no WITH Mno,page WITH Mpage SELECT B ENDDO @ 13,25 SAY "Adding Authors and Categories" SELECT C USE tempB APPEND FROM VOL&V.B&N..TXT SDF DELETE ALL FOR category=" " .AND. author=" " PACK SELECT A GO TOP SET RELATION TO RECNO() INTO C REPLACE ALL author WITH C->author,category WITH C->category SET RELATION @ 13,25 SAY " HOUSEKEEPING " SELECT B ZAP SELECT C ZAP CLEAR CLEAR ALL QUIT - - - - - - - - - - TEMPA.DBF Structure: | PCMAGNDX.DBF Structure: 001 TITLE C 033 | 001 TITLE C 050 002 SUBJECT C 036 | 002 SUBJECT C 080 003 VOL C 002 | 003 CATEGORY C 020 004 NO C 003 | 004 AUTHOR C 033 005 PAGE C 004 | 005 VOL C 002 | 006 NO C 003 TEMPB.DBF Structure: | 007 PAGE C 004 001 CATEGORY C 021 002 AUTHOR C 033 ----------------------------------------------------------------- Converting dBASE's Date Fields (PC Magazine Vol 4 No 18 Sept 3, 1985 Power User) The Date field may cause confusion to new dBASE III users. This new data type has several unique features that demand knowledge and effort to be useful. Here are a few shortcuts that are not explained in the manual but do work. 1. When converting Character data types to Date types, normally it is necessary to use the special CTOD function. However, if a file already has an entire field of data in MM/DD/YY format, and you wish to change this to a Date type in order to obtain the special properties of this data type, all you need to do is MODIFY the STRUCTURE of the field. There is no need to create another field and use the CTOD function to replace the original field. This can be useful for converting either dBASE II files or imported files. 2. Be extremely careful when using this data type in logical operands. If there is no date present in the field, and you are using an operand of any kind on this field, the record will not be selected. This is because the absence of a date yields a null variable rather than a 0 date. A null variable is neither greater than, less than, nor equal to any value. 3. The month feature will work as a selection criterion with a date field for any given file. This can be particularly useful for searching anniversary dates. Editor's Note: A word of caution -- when converting Character type dates to dBASE III's Date type, the data must be in MM/DD/YY or MM-DD-YY format. Dates in any other notation, such as YY/MM/DD, do not convert accurately using MODIFY STRUCTURE alone. Also, this conversion is a one-way street. Date type fields become strings of unexpected, and wierd, numbers when you try to change them into Character fields through MODIFY STRUCTURE. ----------------------------------------------------------------- Same Time Next Month (PC World November 1985 Star-Dot-Star) dBASE III performs many more date calculations than dBASE II, but it's not capable of moving forward or backward in full months while maintaining the same day of the month, for instance, from January 5 to February 5. The routines MTHFWRD.PRG and MTHBACK.PRG do this. Keep these programs in a procedure file and call them when needed. The programs make adjustments for months that have different numbers of days. For example, when moving forward a month from January 30, the program calculates February 28. Similarly, when going backward, March 30 becomes February 28. *MTHFWRD.PRG: Calculates forward from the starting date. Variables are: * MDATE_FWD is supplied by the calling program set to the starting date * and will be returned as the future date * MCOUNT is supplied by the calling program as a counter for the * number of months to go forward in time * MTH, YR are used internally, then released YR = year(MDATE_FWD) MTH = month(MDATE_FWD) + MCOUNT do while MTH > 12 MTH = MTH - 12 YR = YR + 1 enddo MDATE_FWD=ctod(str(MTH,2)+'/'+str(day(MDATE_FWD),2)+'/'+str(YR,4)) if month(MDATE_FWD) > MTH MDATE_FWD = MDATE_FWD - day(MDATE-FWD) endif release MTH,YR - - - - - *MTHBACK.PRG: Calculates backward from the starting date. Variables are * MDATE_FWD is supplied by the calling program set to the starting date * and will be returned as the prior date * MCOUNT is supplied by the calling program as a counter for the * number of months to go backward in time * MTH, YR are used internally, then released YR = year(MDATE_FWD) MTH = month(MDATE_FWD) - MCOUNT do while MTH < 1 MTH = MTH + 12 YR = YR - 1 enddo MDATE_FWD=ctod(str(MTH,2)+'/'+str(day(MDATE_FWD),2)+'/'+str(YR,4)) MDATE_FWD = MDATE_FWD - day(MDATE_FWD) endif release MTH, YR ----------------------------------------------------------------- Screen Utilities for dBASE III (PC Magazine Vol 4 No 18 Sept 3, 1985 Power User) When developing applications using dBASE III, it is often desirable to center or right-justify output to the screen or printer. CENTER.PRG and RIGHT.PRG allow for this type of justification without having to count characters or spaces. Line by line, the program works as follows: Line 1 establishes the parameters that need to be passed when the program is called. Line 2 establishes the width of the output device. The value shown, 80, is generally used for screen output. This can be changed to 133 if output is to a wide-carriage printer. Lines 3 and 4 are used to preserve the cursor's position at the time the program is called. Line 5 computes the starting column of the text in STRING and sends it to the output device. Finally, line 6 resets the cursor to its position at the time of the program call. Editor's Note: These two short routines can be useful for displaying program titles, help messages, and other text on screen or in your custom reports. If you prefer, you can eliminate lines 3, 4 and 6, which will cause the screen cursor to reappear on the line below your message text. CENTERII.PRG and RIGHTII.PRG shows how dBASE II users can achieve the same effect. While the routine lacks dBASE III's elegance (because there isn't a PARAMETERS command in II), the crucial line performing the necessary row+column calculations works exactly the same in both versions of dBASE and runs just as quickly. Macros are used to demonstrate how the program works. You can achieve the same results using your row/column parameters directly. This permits you to write only one line into your command file for each screen message. Using macros, however, does allow you to copy the routines into any of your files. You change the results by changing the numbers and text string stored at the top of the routines. * CENTER.PRG -- Centers text messages on screen/dBASE III PARAMETERS ROW,STRING WIDTH=80 R=ROW() C=COL() @ ROW,0+(WIDTH-(LEN(STRING)))/2 SAY STRING @ R,C SAY ' ' * RIGHT.PRG -- Right-justifies messages on screen/dBASE III PARAMETERS ROW,STRING WIDTH=80 R=ROW() C=COL() @ ROW,0+(WIDTH-(LEN(STRING))) SAY STRING @ R,C SAY ' ' * CENTERII.PRG -- Centers text strings on screen/dBASE II STROE "YOUR CENTERED TEXT" TO STRING STORE 5 TO ROW STORE 80 TO WIDTH @ ROW,0+(WIDTH-(LEN(STRING)))/2 SAY STRING RETURN * RIGHTII.PRG -- Right-justifies text on screen/dBASE II STORE "YOUR FLUSH-RIGHT TEXT" TO STRING STORE 5 TO ROW STORE 80 TO WIDTH @ ROW,0+(WIDTH-(LEN(STRING))) SAY STRING RETURN ----------------------------------------------------------------- dBASE-WordStar/MailMerge Connection (PC Magazine Vol 4 No 18 Sept 3, 1985 Power User) Users of both dBASE and WordStar/MailMerge might find it useful to use the two together to produce personalized form letters. However, certain peculiarities in each program require that special programming be used so that MailMerge sees what it needs to perform properly with all kinds of data types. Problems that often occur when attempting to use dBASE's facilities directly -- without programming -- include unwanted trailing blanks and, word, embedded commas in the data file that confuse MailMerge to the point where it won't track the data correctly. Both of these problems are corrected by the dBASE program below. Basically, the program delineates field data with both quotation marks and commas, an overkill method that is acceptable to MailMerge. It also strips trailing blanks using the TRIM function. As an added bonus, the program can be used with any data file simply by changing the names of the fields used within the DO WHILE loop. Editor's Note: The program can be used by both dBASE II and III. EOF must be changed to EOF() for used with dBASE III. For the sake of clarity, an additional space is used between commas, single-quotes and double-quotes in the listing. For proper results do not include these spaces when typing the program into your system. Structure of SAMPLE.DBF FLD NAME TYPE WIDTH 001 MR_MRS C 004 002 FIRSTNAME C 015 003 LASTNAME C 015 004 COMPANY C 015 005 STREET C 020 006 CITY C 020 007 STATE C 004 008 ZIP C 005 * MAILFILE.PRG - Converts data files for use with MailMerge USE sample.dbf SET TALK OFF SET RAW ON SET ALTERNATE TO datatext.txt SET ALTERNATE ON DO WHILE .NOT. EOF ? ' " ' , TRIM(mr_mrs) , ' " ' , ' , ' ? ' " ' , TRIM(firstname) , ' " ' , ' , ' ? ' " ' , TRIM(lastname) , ' " ' , ' , ' ? ' " ' , TRIM(company) , ' " ' , ' , ' ? ' " ' , TRIM(address) , ' " ' , ' , ' ? ' " ' , TRIM(city) , ' " ' , ' , ' ? ' " ' , TRIM(state) , ' " ' , ' , ' ? ' " ' , TRIM(zip) , ' " ' , ' , ' SKIP ENDDO SET ALTERNATE OFF RETURN ----------------------------------------------------------------- DB3-RAC.TIP An undocumented feature of dBASE III is the ability to simulate Symphony's DOS.APP feature using dBASE III. DOS.APP enables you to exit from Symphony to DOS temporarily, perform function(s) you need and return to Symphony with your work intact, i.e., there is no need to exit Symphony, do other work, and then reload Symphony and your worksheet again. dBASE III has a RUN command, which seems to allow you to exit to DOS and run one EXE or COM file; that's what the documentation says. As it turns out, that is not quite true. I gave dBASE III the command RUN COMMAND.COM. It put me into DOS so I could run whatever I wanted. Once I was finished, I entered the command EXIT, and I was then back in dBASE III. I have found nothing so far that I can't do while in DOS. I did find, however, that to make the above RUN command work, I needed to have a floppy in drive A with COMMAND.COM on it even though I was using a hard disk system. Ron Cleaver 6958 Hanover Parkway #301 Greenbelt, MD 20770